﻿using System;
using System.Collections.Generic;
using System.Text;
using RockCompany.Common.Lib;
using RockCompany.Common.Constant;
using RockCompany.Master.DTO;
using RockCompany.Master.Constant;
using System.Data;
using System.Data.SqlClient;

namespace RockCompany.Master.Model
{
    public class SI04FormModel : CommonModel
    {
        #region Properties
        private DataTable _matRes;
        public DataTable MatRes
        {
            get { return _matRes; }
            set { _matRes = value; }
        }

        private DataTable _prodRes;
        public DataTable ProdRes
        {
            get { return _prodRes; }
            set { _prodRes = value; }
        }
        #endregion

        #region Constructor
        public SI04FormModel()
        {
            dbConnector = new DBConnector();
            DataList = new List<object>();
            MatRes = new DataTable();
            ProdRes = new DataTable();
        }
        #endregion

        #region Utility Methods
        public DataTable matTypeLOV(int type)
        {
            DataTable temp = null;
            if (type > -1)
            {
                switch (type)
                {
                    case SI04Const.NO_MAT_TYPE:
                        string sClause = "SELECT DISTINCT " + SI04Const.NO_MAT_TYPE + " AS " + SI04Const.S_TYPE_NAME + ",";
                        sClause += NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU;
                        sClause += " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE;
                        sClause += " GROUP BY " + NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU;
                        temp = selectData(sClause);
                        break;
                    case SI04Const.NO_PROD_TYPE:
                        temp = new DataTable();
                        temp.Columns.Add(SI04Const.S_TYPE_NAME);
                        temp.Columns.Add(NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU);
                        DataRow r = temp.NewRow();
                        r.SetField<int>(SI04Const.S_TYPE_NAME, SI04Const.NO_PROD_TYPE);
                        r.SetField<string>(NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU, "Thành phẩm/成品");
                        temp.Rows.Add(r);
                        break;
                }
            }
            else
            {
                string sClause = "SELECT DISTINCT " + SI04Const.NO_MAT_TYPE + " AS " + SI04Const.S_TYPE_NAME + ",";
                sClause += NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU;
                sClause += " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE;
                sClause += " GROUP BY " + NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU;
                temp = selectData(sClause);
                DataRow r = temp.NewRow();
                r.SetField<int>(SI04Const.S_TYPE_NAME, SI04Const.NO_PROD_TYPE);
                r.SetField<string>(NGUYEN_VLIEU_VTU_DTO.N_TEN_LOAI_NVL_VTU, "Thành phẩm/成品");
                temp.Rows.Add(r);
            }
            return temp;
        }

        private string getNamThang(int month, int year, bool isPrev)
        {
            string cur_nam_thang = string.Empty;
            if (month == 1)
            {
                cur_nam_thang = Convert.ToString((isPrev ? year - 1 : year)) + "0" + Convert.ToString((isPrev ? 12 : month));
            }
            else
            {
                if (month < 10)
                {
                    cur_nam_thang = Convert.ToString(year) + "0" + Convert.ToString((isPrev ? month - 1 : month));
                }
                else
                {
                    cur_nam_thang = Convert.ToString(year);
                    if (isPrev && month - 1 < 10)
                        cur_nam_thang += "0";
                    cur_nam_thang += Convert.ToString((isPrev ? month - 1 : month));
                }
            }
            return cur_nam_thang;
        }

        public DataRow getCheckedRow(string sNo, int type)
        {
            DataRow curRow = null;
            switch (type)
            {
                case SI04Const.NO_MAT_TYPE:
                    curRow = MatRes.Select(NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU + " = '" + sNo + "'")[0];
                    break;
                case SI04Const.NO_PROD_TYPE:
                    curRow = ProdRes.Select(CONG_LENH_SX.N_SO_LO + " = '" + sNo + "'")[0];
                    break;
            }
            curRow.SetField<bool>(TON_KHO_DTO.N_CHON, !Util.parseBoolean(curRow[TON_KHO_DTO.N_CHON]));
            return curRow;
        }

        public DataTable getSelectedMats(int type)
        {
            DataTable res = null;
            switch (type)
            {
                case SI04Const.NO_MAT_TYPE:
                    res = MatRes.Clone();
                    foreach (DataRow row in DataList)
                    {
                        res.ImportRow(row);
                    }
                    break;
                case SI04Const.NO_PROD_TYPE:
                    res = ProdRes.Clone();
                    foreach (DataRow row in DataList)
                    {
                        res.ImportRow(row);
                    }
                    break;
            }          
            return res;
        }
        #endregion

        #region Main Methods
        public DataTable seacrhWH(int thang, int nam, int type,string sMatNo, string mat_type)
        {
            string sSql = "";
            switch (type)
            {
                case SI04Const.NO_MAT_TYPE:
                    sSql = "SELECT ";
                    sSql += "CONVERT(BIT,'0') AS " + TON_KHO_DTO.N_CHON + ",";
                    sSql += TON_KHO_DTO.N_SL_DAU + ",";
                    sSql += TON_KHO_DTO.N_SL_NHAP + ",";
                    sSql += TON_KHO_DTO.N_SL_XUAT + ",";
                    sSql += TON_KHO_DTO.N_SL_CUOI + ",";
                    sSql += TON_KHO_DTO.N_TABLE + "." + TON_KHO_DTO.N_GHI_CHU + ",";
                    sSql += TON_KHO_DTO.N_MA_HANG + " AS " + NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU;
                    sSql += " FROM " + TON_KHO_DTO.N_TABLE;
                    sSql += " WHERE " + TON_KHO_DTO.N_NAM_THANG + " = '" + getNamThang(thang, nam, false) + "'";                    
                    sSql += " AND " + TON_KHO_DTO.N_MA_MAU + " IS NULL";
                    sSql += " AND " + TON_KHO_DTO.N_MA_HANG + " LIKE '" + sMatNo + "%'";
                    break;
                case SI04Const.NO_PROD_TYPE:
                    sSql = "SELECT CONVERT(BIT,'0') AS " + TON_KHO_DTO.N_CHON + ",";
                    sSql += " A1." + TON_KHO_DTO.N_MA_HANG + " AS '" + CONG_LENH_SX.N_SO_LO + "',";
                    sSql += "CONVERT(BIT,'0') AS " + TON_KHO_DTO.N_CHON + ",";
                    sSql += " A1." + TON_KHO_DTO.N_MA_MAU + " AS '" + TON_KHO_DTO.N_MA_MAU + "',";
                    sSql += " A1." + TON_KHO_DTO.N_SL_DAU + ",";
                    sSql += " A1." + TON_KHO_DTO.N_SL_NHAP + ",";
                    sSql += " A1." + TON_KHO_DTO.N_SL_XUAT + ",";
                    sSql += " A1." + TON_KHO_DTO.N_SL_CUOI + ",";
                    sSql += " ISNULL(B1." + CONG_LENH_SX.N_SL_TON_AN_TOAN + ",NULL) AS '" + CONG_LENH_SX.N_SL_TON_AN_TOAN + "'";
                    sSql += " FROM((SELECT *";
                    sSql += " FROM " + TON_KHO_DTO.N_TABLE;
                    sSql += " WHERE " + TON_KHO_DTO.N_NAM_THANG + " = '" + getNamThang(thang, nam, false) + "'";
                    sSql += " AND " + TON_KHO_DTO.N_MA_MAU + " IS NOT NULL";
                    sSql += " AND " + TON_KHO_DTO.N_MA_HANG + " LIKE '" + sMatNo + "%') A1";
                    sSql += " LEFT JOIN(SELECT *";
                    sSql += " FROM " + CONG_LENH_SX.N_TABLE + ") B1";
                    sSql += " ON A1." + TON_KHO_DTO.N_MA_HANG + " = B1." + CONG_LENH_SX.N_SO_LO + ") ";
                    return selectData(sSql);
                    break;
            }
            return selectData(sSql);
        }

        public void calWH(int year, int month, int type, string matType)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            pv.Add(SI04Const.P_NAM, year);
            pv.Add(SI04Const.P_THANG, month);
            pv.Add(SI04Const.P_NAMTHANG, getNamThang(month, year, true));
            //pv.Add(SI04Const.P_MAT_TYPE, matType);
            switch (type)
            {
                case SI04Const.NO_MAT_TYPE:
                    pv.Add(SI04Const.P_TYPE, SI04Const.NO_MAT_TYPE);
                    MatRes = excuteProcedure(SI04Const.PROC_BUILD_TON_KHO, pv);
                    if (!MatRes.Columns.Contains(TON_KHO_DTO.N_GHI_CHU))
                        MatRes.Columns.Add(TON_KHO_DTO.N_GHI_CHU, typeof(string));
                    if (!MatRes.Columns.Contains(TON_KHO_DTO.N_CHON))
                    {
                        MatRes.Columns.Add(TON_KHO_DTO.N_CHON, typeof(bool));
                        MatRes.Columns[TON_KHO_DTO.N_CHON].DefaultValue = false;
                    }
                    MatRes.Columns[TON_KHO_DTO.N_SL_CUOI].Expression = TON_KHO_DTO.N_SL_DAU + "+" + TON_KHO_DTO.N_SL_NHAP + "-" + TON_KHO_DTO.N_SL_XUAT;
                    break;
                case SI04Const.NO_PROD_TYPE:
                    pv.Add(SI04Const.P_TYPE, SI04Const.NO_PROD_TYPE);
                    ProdRes = excuteProcedure(SI04Const.PROC_BUILD_TON_KHO, pv);
                    if (!ProdRes.Columns.Contains(TON_KHO_DTO.N_GHI_CHU))
                        ProdRes.Columns.Add(TON_KHO_DTO.N_GHI_CHU, typeof(string));
                    if (!ProdRes.Columns.Contains(TON_KHO_DTO.N_CHON))
                    {
                        ProdRes.Columns.Add(TON_KHO_DTO.N_CHON, typeof(bool));
                        ProdRes.Columns[TON_KHO_DTO.N_CHON].DefaultValue = false;
                    }
                    ProdRes.Columns[TON_KHO_DTO.N_SL_CUOI].Expression = TON_KHO_DTO.N_SL_DAU + "+" + TON_KHO_DTO.N_SL_NHAP + "-" + TON_KHO_DTO.N_SL_XUAT;
                    break;
            }
        }

        public bool updateWH(string namthang, DataRow newRows, int nType)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            string sWhereClause = "";
            switch (nType)
            {
                case SI04Const.NO_MAT_TYPE:
                    sWhereClause = TON_KHO_DTO.N_NAM_THANG + " = '" + namthang + "'";
                    sWhereClause += " AND " + TON_KHO_DTO.N_MA_HANG + " = '" + newRows.Field<string>(NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU) + "'";
                    pv.Add(TON_KHO_DTO.N_NAM_THANG, namthang);
                    pv.Add(TON_KHO_DTO.N_MA_HANG, newRows.Field<string>(NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU));
                    pv.Add(TON_KHO_DTO.N_SL_DAU, newRows.Field<decimal>(TON_KHO_DTO.N_SL_DAU));
                    pv.Add(TON_KHO_DTO.N_SL_NHAP, newRows.Field<decimal>(TON_KHO_DTO.N_SL_NHAP));
                    pv.Add(TON_KHO_DTO.N_SL_XUAT, newRows.Field<decimal>(TON_KHO_DTO.N_SL_XUAT));
                    pv.Add(TON_KHO_DTO.N_SL_CUOI, newRows.Field<decimal>(TON_KHO_DTO.N_SL_CUOI));
                    pv.Add(TON_KHO_DTO.N_GHI_CHU, Util.parseString(newRows[TON_KHO_DTO.N_GHI_CHU]));
                    break;
                case SI04Const.NO_PROD_TYPE:
                    sWhereClause = TON_KHO_DTO.N_NAM_THANG + " = '" + namthang + "'";
                    sWhereClause += " AND " + TON_KHO_DTO.N_MA_HANG + " = '" + newRows.Field<string>(CONG_LENH_SX.N_SO_LO) + "'";
                    pv.Add(TON_KHO_DTO.N_NAM_THANG, namthang);
                    pv.Add(TON_KHO_DTO.N_MA_HANG, newRows.Field<string>(CONG_LENH_SX.N_SO_LO));
                    pv.Add(TON_KHO_DTO.N_SL_DAU, newRows.Field<decimal>(TON_KHO_DTO.N_SL_DAU));
                    pv.Add(TON_KHO_DTO.N_SL_NHAP, newRows.Field<decimal>(TON_KHO_DTO.N_SL_NHAP));
                    pv.Add(TON_KHO_DTO.N_SL_XUAT, newRows.Field<decimal>(TON_KHO_DTO.N_SL_XUAT));
                    pv.Add(TON_KHO_DTO.N_SL_CUOI, newRows.Field<decimal>(TON_KHO_DTO.N_SL_CUOI));
                    pv.Add(TON_KHO_DTO.N_GHI_CHU, Util.parseString(newRows[TON_KHO_DTO.N_GHI_CHU]));
                    break;
            }
            return updateData2(TON_KHO_DTO.N_TABLE, sWhereClause, pv);
        }

        public bool insertWH(string namthang, DataRow newRows, int nType)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            switch (nType)
            {
                case SI04Const.NO_MAT_TYPE:
                    pv.Add(TON_KHO_DTO.N_NAM_THANG, namthang);
                    pv.Add(TON_KHO_DTO.N_MA_HANG, newRows.Field<string>(NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU));
                    pv.Add(TON_KHO_DTO.N_SL_DAU, newRows.Field<decimal>(TON_KHO_DTO.N_SL_DAU));
                    pv.Add(TON_KHO_DTO.N_SL_NHAP, newRows.Field<decimal>(TON_KHO_DTO.N_SL_NHAP));
                    pv.Add(TON_KHO_DTO.N_SL_XUAT, newRows.Field<decimal>(TON_KHO_DTO.N_SL_XUAT));
                    pv.Add(TON_KHO_DTO.N_SL_CUOI, newRows.Field<decimal>(TON_KHO_DTO.N_SL_CUOI));
                    pv.Add(TON_KHO_DTO.N_GHI_CHU, Util.parseString(newRows[TON_KHO_DTO.N_GHI_CHU]));
                    break;
                case SI04Const.NO_PROD_TYPE:
                    pv.Add(TON_KHO_DTO.N_NAM_THANG, namthang);
                    pv.Add(TON_KHO_DTO.N_MA_HANG, newRows.Field<string>(CONG_LENH_SX.N_SO_LO));
                    pv.Add(TON_KHO_DTO.N_MA_MAU, newRows.Field<string>(TON_KHO_DTO.N_MA_MAU));
                    pv.Add(TON_KHO_DTO.N_SL_DAU, newRows.Field<decimal>(TON_KHO_DTO.N_SL_DAU));
                    pv.Add(TON_KHO_DTO.N_SL_NHAP, newRows.Field<decimal>(TON_KHO_DTO.N_SL_NHAP));
                    pv.Add(TON_KHO_DTO.N_SL_XUAT, newRows.Field<decimal>(TON_KHO_DTO.N_SL_XUAT));
                    pv.Add(TON_KHO_DTO.N_SL_CUOI, newRows.Field<decimal>(TON_KHO_DTO.N_SL_CUOI));
                    pv.Add(TON_KHO_DTO.N_GHI_CHU, Util.parseString(newRows[TON_KHO_DTO.N_GHI_CHU]));
                    break;
            }
            return insertData(TON_KHO_DTO.N_TABLE, pv);
        }

        public bool saveData(int thang, int nam, int type, string mat_type)
        {
            DataTable existData = seacrhWH(thang, nam, type, "", mat_type);
            DataTable temDB = null;
            int insertRows = 0, updateRows = 0;
            string sNo = "";

            switch (type)
            {
                case SI04Const.NO_MAT_TYPE:
                    temDB = MatRes;
                    sNo = NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU;
                    break;
                case SI04Const.NO_PROD_TYPE:
                    temDB = ProdRes;
                    sNo = CONG_LENH_SX.N_SO_LO;
                    break;
            }

            foreach (DataRow row in temDB.Rows)
            {
                if (Util.parseDouble(row[TON_KHO_DTO.N_SL_CUOI].ToString()) > 0)
                {
                    string sNewNo = Util.parseString(row[sNo]);
                    if (!(existData.Select(sNo + " ='" + sNewNo + "'").Length > 0))
                    {
                        if (insertWH(getNamThang(thang, nam, false), row, type))
                        {
                            insertRows++;
                        }
                    }
                    else
                    {
                        if (updateWH(getNamThang(thang, nam, false), row, type))
                        {
                            updateRows++;
                        }
                    }
                }
            }
            return insertRows > 0 || updateRows > 0;
        }

        public bool export2Excel(string newPath, string sWH_Time, DataTable tempDB)
        {
            bool b = false;
            ExcelExporter excelExport = new ExcelExporter();
            excelExport.Trans = this.translator;
            if (excelExport.open(SI04Const.EX_NAME))
            {
                excelExport.inputValue(SI04Const.EX_POS_PRINT_DATE, DateTime.Now.ToShortTimeString());
                excelExport.inputValue(SI04Const.EX_POS_WH_TIME, sWH_Time);

                List<DataColumn> disCol = new List<DataColumn>();
                if (tempDB.Equals(MatRes))
                    disCol.Add(tempDB.Columns[NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU]);
                else
                {
                    disCol.Add(tempDB.Columns[CONG_LENH_SX.N_SO_LO]);
                    disCol.Add(tempDB.Columns[CONG_LENH_SX.N_MA_MAU]);
                }
                disCol.Add(tempDB.Columns[TON_KHO_DTO.N_SL_DAU]);
                disCol.Add(tempDB.Columns[TON_KHO_DTO.N_SL_NHAP]);
                disCol.Add(tempDB.Columns[TON_KHO_DTO.N_SL_XUAT]);
                disCol.Add(tempDB.Columns[TON_KHO_DTO.N_SL_CUOI]);

                excelExport.inputRangeValues(tempDB, disCol, SI04Const.EX_BEGIN_GRID_COL, SI04Const.EX_BEGIN_GRID_ROW, true);
                b = excelExport.saveExcel(newPath);
                excelExport.close();
            }
            return b;
        }
        #endregion
    }
}
